package chapter14;

import com.mysql.cj.jdbc.MysqlDataSource;

import java.sql.*;

/**
 * Created with IntelliJ IDEA.
 * Description:
 *
 * @author lzy
 * @create 2018-03-26 上午9:04
 */
public class MySQLTest {
    public static void main(String[] args) {
//        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://192.168.100.66/java_test?useSSL=false&serverTimezone=UTC";
        String url2 = "jdbc:mysql://192.168.100.66/java_test?useSSL=false&serverTimezone=UTC";
        String userName = "root";
        String password = "crscd123@";

//        try {
//            Class.forName(driver);
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//            return;
//        }

        MysqlDataSource ds = new MysqlDataSource();
        ds.setURL(url2);
        ds.setUser(userName);
        ds.setPassword(password);

        try {
            System.out.println("get connection from data source");
            Connection connection = ds.getConnection();
            System.out.println(ds.getDatabaseName());
            System.out.println(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try (Connection conn = DriverManager.getConnection(url, userName, password);
             Statement statement = conn.createStatement()) {
            System.out.println(conn);
            /*
            String sql_drop = "DROP TABLE IF EXISTS book;";
            statement.execute(sql_drop);
            */
            String sql = "CREATE TABLE IF NOT EXISTS book(\n" +
                    "  id    INT NOT NULL AUTO_INCREMENT,\n" +
                    "  name  VARCHAR(50),\n" +
                    "  price DOUBLE,\n" +
                    "  PRIMARY KEY (id)\n" +
                    ")\n" +
                    "  ENGINE = InnoDB\n" +
                    "  DEFAULT CHARSET = utf8;";
            statement.execute(sql);
            System.out.println("create table book.");

            String sql_insert = "INSERT INTO book (name, price) VALUES ('test1',123.3);";
            long count = statement.executeUpdate(sql_insert, Statement.RETURN_GENERATED_KEYS);
            ResultSet r = statement.getGeneratedKeys();
            if (r.next()) {
                int key = r.getInt(1);
                System.out.println("insert auto gen key is " + key);
            }
            count += statement.executeUpdate(sql_insert);
            count += statement.executeUpdate(sql_insert);

            System.out.println("insert into db:" + count);

            String sql_query = "SELECT * FROM book";
            ResultSet rs = statement.executeQuery(sql_query);
            while (rs.next()) {
                long id = rs.getInt("id");
                String name = rs.getString("name");
                double price = rs.getDouble("price");
                System.out.println(String.format("id:%d,name:%s,price:%5.2f", id, name, price));
            }

            String sql_update = "UPDATE book SET name='test2' WHERE name='test1'";
            count = statement.executeUpdate(sql_update);
            System.out.println("update db:" + count);
            String sql_del = "DELETE FROM book WHERE name='test2'";
            count = statement.executeUpdate(sql_del);
            System.out.println("del from db:" + count);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
